#!/usr/bin/env python
# coding=utf-8



import sys

import pandas as pd

import datetime,time


import logging


logging.basicConfig()
LOG = logging.getLogger('toexcel')
LOG.setLevel(logging.DEBUG)
# 创建一个handler，用于写入日志文件
fh = logging.FileHandler('toexcel.log')
fh.setLevel(logging.DEBUG)
# 定义handler的输出格式
LOG_FORMAT = logging.Formatter('%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s')
fh.setFormatter(LOG_FORMAT)
# 给logger添加handler
LOG.addHandler(fh)

# 再创建一个handler，用于输出到控制台
ch = logging.StreamHandler()
ch.setLevel(logging.DEBUG)
ch.setFormatter(LOG_FORMAT)
LOG.addHandler(ch)

import numpy as np
import xlrd
import xlwt


sys.path.append('/data/online/youjia_script/com/youjia/util')
# sys.path.append('D:/youjia201811/youjia_report_new/com/youjia/util')
import calendar


from send_util import sendBigEmail

from db_config import get_config_51,get_config_test_69,get_config_13_1



# 客服 2018-10-13
def base():
    return '''
                SELECT c.short_name 城市,org.`name` 门店,usc.sname 服务中心,CONCAT(ich.house_id,'') 房屋id,
        u1.realname 招商经理,
        case ich.house_product_type 
        when 10 then '标准房'
        when 20 then '严选房'
        else ''
        end 产品类型,
        u2.realname BD,users.realname 所属投资人,
        case ich.state when 100 then '编辑中' when 0 then '待审核' 
                       when 10 then '审核通过' when 20 then '审核驳回' 
                       when 30 then '合同解约' when 40 then '签约失败' 
                       when 50 then '合同到期' end 合同状态,
        CASE ich.cancel_type WHEN 10 THEN '业主解约'
                 WHEN 20 THEN '不可抗力因素'
                 WHEN 30 THEN '其他'
                 WHEN 40 THEN '签约失败'
            ELSE '' END 解约原因,
        case h.state WHEN 0 then '房屋待签约' 
            WHEN 1 then '加盟失败'
            WHEN 6 THEN 'BD交付'
            WHEN 10 THEN '筹建中'
            WHEN 15 THEN '筹建待验收'
            WHEN 20 THEN '待合并处理'
            WHEN 30 THEN '待上线'
            WHEN 100 THEN '已上线'
            WHEN 110 THEN '已下线'
            WHEN 120 THEN '违约下线'
            WHEN 130 then '合同到期'
            ELSE '' END 房屋状态,
         concat(case lht.transfer_state WHEN 100 THEN '交付成功' WHEN 110 THEN '预算清单已上传' 
              WHEN 120 THEN '硬装完成统购到货' WHEN 130 THEN '已通知验收' WHEN 140 THEN '已验收' else '' END ,'-',
                case lht.check_house_result when 0 then '可上线' when 10 then '上线后整改' when 20 then '整改后上线' end) 筹建状态_验房结果,
        u10.realname 筹建人员,h.address 物业地址,h.room_num 居室,ich.rent_grow_ratio 每年租金,
        ich.agency_fee/100 中介费,
        ich.property_fee/100 物业费,
        ich.heating_fee/100 取暖费,
        ich.net_fee/100 网络带宽费,
        ich.forecast_cycle 回报周期,
        u3.realname 业主姓名,
        u3.mobile 业主手机号,
        concat(ich.lease_date_begin, '  -  ', ich.lease_date_end) 房屋承租日期,
        concat( (case hho.state WHEN 0 THEN '未预约交付 ' WHEN 10 THEN '已预约交付' WHEN 20 THEN '已交付'  ELSE '' END)  , '--',
            (CASE hand_over_result WHEN 1 THEN '完成交付' WHEN 2 THEN '通知BD跟进' ELSE  '' END )) 交付状态,
        if(pp1.create_time IS NOT NULL, '是','否')是否交付筹建,
        concat(ich.create_time,'') 租房合同创建时间,
        CONCAT(ich.audit_pass_time,'') 合同审核通过时间,
        CONCAT(pp1.create_time,'') 交付筹建时间,
        CONCAT(pp2.create_time,'') 上传清单时间,
        CONCAT(pp3.create_time,'')  硬装完成时间,
        CONCAT(pp4.create_time,'')  统购物资到货时间,
        concat(lht.notice_check_time,'') 通知验收时间,
        CONCAT(pp5.create_time,'') 验收通过时间,
        CONCAT(pp6.create_time,'') 保洁完成时间,
        (CASE l.real_shot_state WHEN 0 THEN '未实拍' WHEN 10 THEN '照片审核中' WHEN 20 THEN '审核通过' WHEN 30 THEN '审核拒绝' ELSE 
'' END) 实拍状态,
                CONCAT(l.real_shot_time,'')  实拍时间,
        CONCAT(ich.lease_date_begin,'--',ich.lease_date_end) 托管日期,
        CONCAT(ich.decorate_date_begin,'--',ich.decorate_date_end) 装修期,
        CONCAT(lho.create_time,'') 上线时间

        FROM  youjia.invest_compact_house ich
        LEFT JOIN youjia.lod_house h ON ich.house_id=h.id
        LEFT JOIN youjia.city c ON h.city_id=c.id
        LEFT JOIN youjia.user_org org ON org.id=h.work_id
        LEFT JOIN youjia.user_org_service_center usc on h.service_id=usc.sid
        left join youjia.invest_compact ic on ic.id=ich.invest_compact_id
        left join youjia.users u1 on u1.id=ic.business_id
        left join youjia.users u2 on u2.id=ich.business_id
        left join youjia.users u3 on u3.id=ich.landlord_id
        left join youjia.users on users.id=ic.invest_id
        left join youjia.lod_house_transfer lht on lht.house_id = h.id
        left JOIN youjia.lod_house_hand_over hho ON hho.lod_house_id=h.id
        LEFT JOIN youjia.users u10 ON hho.builder_id=u10.id
        LEFT JOIN (
        SELECT m1.house_id,m1.create_time FROM(
        SELECT op1.house_id,op1.create_time FROM youjia.lod_operations op1
        WHERE op1.ops_type=1003 AND op1.operate_result<>2
        ORDER BY op1.create_time DESC
        ) m1
        GROUP BY m1.house_id
        )pp1 ON h.id=pp1.house_id
        LEFT JOIN (
        SELECT m1.house_id,m1.create_time FROM(
        SELECT op1.house_id,op1.create_time FROM youjia.lod_operations op1
        WHERE op1.ops_type=110 AND op1.operate_result<>2
        ORDER BY op1.create_time DESC
        ) m1
        GROUP BY m1.house_id
        )pp2 ON h.id=pp2.house_id

        LEFT JOIN (
        SELECT m1.house_id,m1.create_time FROM(
        SELECT op1.house_id,op1.create_time FROM youjia.lod_operations op1
        WHERE op1.ops_type=128 AND op1.operate_result<>2
        ORDER BY op1.create_time DESC
        ) m1
        GROUP BY m1.house_id
        )pp3 ON h.id=pp3.house_id

        LEFT JOIN (
        SELECT m1.house_id,m1.create_time FROM(
        SELECT op1.house_id,op1.create_time FROM youjia.lod_operations op1
        WHERE op1.ops_type=126 AND op1.operate_result<>2
        ORDER BY op1.create_time DESC
        ) m1
        GROUP BY m1.house_id
        )pp4 ON h.id=pp4.house_id

        LEFT JOIN (
        SELECT m1.house_id,m1.create_time FROM(
        SELECT op1.house_id,op1.create_time FROM youjia.lod_operations op1
        WHERE op1.ops_type in (1005,1004) AND op1.operate_result<>2
        ORDER BY op1.create_time DESC
        ) m1
        GROUP BY m1.house_id
        )pp5 ON h.id=pp5.house_id

        LEFT JOIN (
        SELECT m1.house_id,m1.create_time FROM(
        SELECT op1.house_id,op1.create_time FROM youjia.lod_operations op1
        WHERE op1.ops_type =1500 AND op1.operate_result<>2
        ORDER BY op1.create_time DESC
        ) m1
        GROUP BY m1.house_id
        )pp6 ON h.id=pp6.house_id

        LEFT JOIN youjia.lodge l ON h.lodge_id=l.id

        LEFT JOIN (SELECT
                lod_house_id,create_time
                FROM
                    youjia.lod_house_online_time
                WHERE

                    sales_channel_id IS NULL
                    OR sales_channel_id = 4
                GROUP BY lod_house_id
                ORDER BY
                    create_time ASC
                ) lho ON lho.lod_house_id=h.id
        WHERE h.city_id<>376
        AND h.city_id IS NOT NULL
        AND h.work_id IS NOT NULL
        GROUP BY h.id
        ORDER BY h.city_id;
    '''


if __name__ == '__main__':
    today = datetime.date.today()
    db = get_config_51()
    sql_df = pd.read_sql(base(), con=db)
    result_df = sql_df

    # xlsfile = u'D:/rent_compact_new.xlsx'
    xlsfile = u'/data/online/youjia_script/com/youjia/rent_compact_new.xlsx'
    with pd.ExcelWriter(xlsfile, options={'encoding': 'utf-8'}) as writer:
        # result_df.columns = title
        result_df.to_excel(writer, sheet_name=u'data_sjz', index=False, encoding='utf-8')
        writer.save()
        writer.close()
    #  id    ownerid   firstonlinetime  short_name  district_name   street_name  roomnum  area  l.dayprice/100
    print
    "-------------------------------------"
    # print sql2_df
    db.close()

    mail_content = 'Dear all,\n'
    mail_content = mail_content + '    附件为BD租房合同_NEW，请查收！ \n'
    mail_content = mail_content + '谢谢.'
    #sendBigEmail(unicode('BD租房合同_NEW'+'_'+today.strftime("%Y-%m-%d"),'utf-8'),mail_content,xlsfile,'liuguanghua@iyoujia.com;bianzhiwei@iyoujia.com')
    sendBigEmail('BD租房合同_NEW' + '_' + today.strftime("%Y-%m-%d"), mail_content, xlsfile,
                 'dt@iyoujia.com;wangjian@iyoujia.com;zhangwei1@iyoujia.com;chengxuezi@iyoujia.com;')

